package com.rocket.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.database.Cursor;

import com.rocket.entity.Position;
import com.rocket.entity.WorkStation;

public class WorkStationSql {

	private static WorkStationSql	instance					= null;
	private final String			TABLE_NAME					= "WorkStation";

	private final String			KEY_WORKSTATION_ID			= "id";
	private final String			KEY_WORKSTATION_NAME		= "name";
	private final String			KEY_WORKSTATION_NUMBER		= "number";
	private final String			KEY_WORKSTATION_HAIBA		= "haiba";
	private final String			KEY_WORKSTATION_POSITION	= "position";

	public static synchronized WorkStationSql getInstance() {
		if (null == instance) {
			instance = new WorkStationSql();
		}
		return instance;
	}

	public void init() {
		createTable();
		initData();
	}

	private void createTable() {
		if (DataBaseOpenHelper.mDataBase != null) {
			String sql = "drop table if exists " + TABLE_NAME;
			DataBaseOpenHelper.mDataBase.execSQL(sql);
			sql = "create table if not exists " + TABLE_NAME + "( "
					+ KEY_WORKSTATION_ID + " INTEGER PRIMARY KEY,"
					+ KEY_WORKSTATION_NAME + " VARCHAR,"
					+ KEY_WORKSTATION_NUMBER + " VARCHAR,"
					+ KEY_WORKSTATION_HAIBA + " INTEGER,"
					+ KEY_WORKSTATION_POSITION + " VARCHAR)";
			DataBaseOpenHelper.mDataBase.execSQL(sql);
		}
	}

	public long insert(WorkStation station) {
		if (DataBaseOpenHelper.mDataBase != null) {
			ContentValues values = new ContentValues();
			values.put(KEY_WORKSTATION_NAME, station.name);
			values.put(KEY_WORKSTATION_NUMBER, station.number);
			values.put(KEY_WORKSTATION_HAIBA, station.haiba);
			if (station.positions != null) {
				StringBuilder pos = new StringBuilder();
				for (Position p : station.positions) {
					pos.append(p.lon + "/" + p.lat).append(",");
				}
				pos.deleteCharAt(pos.length() - 1);
				values.put(KEY_WORKSTATION_POSITION, pos.toString());
			}
			return DataBaseOpenHelper.mDataBase
					.insert(TABLE_NAME, null, values);
		}
		return 0;
	}

	public long update(WorkStation station) {
		if (DataBaseOpenHelper.mDataBase != null) {
			ContentValues values = new ContentValues();
			values.put(KEY_WORKSTATION_NAME, station.name);
			values.put(KEY_WORKSTATION_NUMBER, station.number);
			values.put(KEY_WORKSTATION_HAIBA, station.haiba);
			if (station.positions != null) {
				StringBuilder pos = new StringBuilder();
				for (Position p : station.positions) {
					pos.append(p.lon + "/" + p.lat).append(",");
				}
				pos.deleteCharAt(pos.length() - 1);
				values.put(KEY_WORKSTATION_POSITION, pos.toString());
			}
			return DataBaseOpenHelper.mDataBase.update(TABLE_NAME, values,
					KEY_WORKSTATION_ID + " = ?", new String[] { String
							.valueOf(station.id) });
		}
		return 0;
	}

	public void delete(WorkStation station) {
		delete(station.id);
	}

	public void delete(int id) {
		if (DataBaseOpenHelper.mDataBase != null) {
			DataBaseOpenHelper.mDataBase.delete(TABLE_NAME, KEY_WORKSTATION_ID
					+ " = ?", new String[] { String.valueOf(id) });
		}
	}

	public ArrayList<WorkStation> query() {
		ArrayList<WorkStation> stations = new ArrayList<WorkStation>();
		if (DataBaseOpenHelper.mDataBase != null) {
			Cursor cur = null;
			try {
				DataBaseOpenHelper.mDataBase.beginTransaction();
				cur = DataBaseOpenHelper.mDataBase.query(TABLE_NAME, null,
						null, null, null, null, null);
				if (cur != null && cur.moveToFirst()) {
					do {
						WorkStation station = new WorkStation();
						station.id = cur.getInt(cur
								.getColumnIndex(KEY_WORKSTATION_ID));
						station.name = cur.getString(cur
								.getColumnIndex(KEY_WORKSTATION_NAME));
						station.number = cur.getString(cur
								.getColumnIndex(KEY_WORKSTATION_NUMBER));
						station.haiba = cur.getInt(cur
								.getColumnIndex(KEY_WORKSTATION_HAIBA));
						station.positions = new ArrayList<Position>();
						String[] ps = cur.getString(
								cur.getColumnIndex(KEY_WORKSTATION_POSITION))
								.split(",");
						for (String s : ps) {
							String[] a = s.split("/");
							Position p = new Position(a[0],	a[1]);
							station.positions.add(p);
						}
						stations.add(station);
					} while (cur.moveToNext());
				}
				DataBaseOpenHelper.mDataBase.setTransactionSuccessful();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (cur != null) {
					cur.close();
					cur = null;
				}
				DataBaseOpenHelper.mDataBase.endTransaction();
			}
		}
		return stations;
	}

	private void initData() {

	}

	public boolean isNameExists(String name) {
		if (DataBaseOpenHelper.mDataBase != null) {
			Cursor cur = DataBaseOpenHelper.mDataBase.query(TABLE_NAME, null,
					KEY_WORKSTATION_NAME + " = ? ", new String[] { name },
					null, null, null);
			if ((cur != null) && (cur.getCount() > 0)) {
				return true;
			}
		}
		return false;
	}

	public boolean isNumberExists(String number) {
		if (DataBaseOpenHelper.mDataBase != null) {
			Cursor cur = DataBaseOpenHelper.mDataBase.query(TABLE_NAME, null,
					KEY_WORKSTATION_NUMBER + " = ? ", new String[] { number },
					null, null, null);
			if ((cur != null) && (cur.getCount() > 0)) {
				return true;
			}
		}
		return false;
	}
}
